package org.compiere.process;

import java.io.File;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MUser;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

public class TurnoverBalance extends SvrProcess {
	/** Current context	*/
	private Properties m_ctx;
	/**	 */
	private ProcessInfo pi;
	/** Account Sign					*/
	private String				AccountSign=null;
	/** */
	private Timestamp			DateAcct_From = null;
	private Timestamp			DateAcct_To = null;
	/**	Client Parameter					*/
	private int					AD_Client_ID = 0;
	/**	Org Parameter					*/
	private int					AD_Org_ID = 0;
	/**	Account Parameter				*/
	private int					Account_ID = 0;
	/**	BPartner Parameter				*/
	private int					C_BPartner_ID = 0;
	/** User Element 1 Parameter		*/
	private int					UserElement1_ID = 0;
	/** */
	private static CLogger	s_log	= CLogger.getCLogger (BalanceSheets.class);
	/** */
	private String path = "";

	/**
	 *  Prepare - e.g., get Parameters.
	 */

	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)
		{
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null);
			else if (name.equals("AD_Client_ID"))
				AD_Client_ID = ((BigDecimal)para[i].getParameter()).intValue();
			else if (name.equals("AD_Org_ID"))
				AD_Org_ID = ((BigDecimal)para[i].getParameter()).intValue();
			else if (name.equals("Account_ID"))
				Account_ID = ((BigDecimal)para[i].getParameter()).intValue();
			else if (name.equals("DateAcct_From"))
				DateAcct_From = (Timestamp)para[i].getParameter();
			else if (name.equals("DateAcct_To"))
				DateAcct_To = (Timestamp)para[i].getParameter();
			else if (name.equals("C_BPartner_ID"))
				C_BPartner_ID = ((BigDecimal)para[i].getParameter()).intValue();
			else if (name.equals("UserElement1_ID"))
				UserElement1_ID = ((BigDecimal)para[i].getParameter()).intValue();
			else if (name.equals("AccountSign"))
				AccountSign = (String)para[i].getParameter();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}
		if(path.isEmpty())
			path = Util.localFilePath;
		else
			path = path + System.getProperty("file.separator");

	}

	@Override
	protected String doIt() throws Exception {
		// Types of cells for
				Label label;
				Number number;
				DateTime date;
				// There are checks for a specified pattern
				String templateName = "tempOSV";
				//
				MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, templateName);
				if(entry == null)
					throw new AdempiereException(Msg.translate(m_ctx, "NotFoundTemplate")+" "+templateName);
				
				// We define a path to generate
				String fileExtension = entry.getName().substring(entry.getName().lastIndexOf("."),entry.getName().length());
				StringBuffer fullPath = new StringBuffer(path); 
				fullPath.append("Temporary Balance Sheets");
				fullPath.append(fileExtension);	
				// 
		        File templateCopy = new File(fullPath.toString());
		        File temporaryFile = entry.getFile(Util.localFilePath+templateName);
				Workbook tableBook = null;
				WritableWorkbook copy = null;
				WritableSheet sheet = null;
				 try {
					 tableBook = Workbook.getWorkbook(temporaryFile);
					 copy = Workbook.createWorkbook(templateCopy, tableBook);
					 sheet = copy.getSheet(0);
		        } catch (Exception e) {
		        	ADialog.error(999, null, "Error tableBook. " + e.getMessage());
		        	return "Error tableBook.";
		        }

		       ExcelCell cellStart =  Util.getCellStart(tableBook,">>");
		       //Styles for cells
		       WritableCellFormat standartstyle = new WritableCellFormat();
		       standartstyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		       standartstyle.setAlignment(Alignment.LEFT);
		       standartstyle.setVerticalAlignment(VerticalAlignment.CENTRE);
		       standartstyle.setWrap(true);
		       //pattern for date
		       SimpleDateFormat format = new SimpleDateFormat("dd.MM.yyyy");
		       //pattern for now date and time
		       Date now = Calendar.getInstance().getTime(); 
		       DateFormat customDateFormat = new DateFormat ("dd.MM.yyyy"); 
		       DateFormat customTimeFormat = new DateFormat("hh:mm:ss");
		       WritableCellFormat dateFormat = new WritableCellFormat (customDateFormat); 
		       WritableCellFormat timeFormat = new WritableCellFormat (customTimeFormat);   
		       //style for bold font
		       WritableFont arial10boldfont = new WritableFont(WritableFont.ARIAL, 10,WritableFont.BOLD);
		       WritableCellFormat arial10boldformat = new WritableCellFormat(arial10boldfont);
		       arial10boldformat.setBorder(Border.LEFT, BorderLineStyle.MEDIUM);
		       arial10boldformat.setBorder(Border.TOP, BorderLineStyle.MEDIUM);
		       arial10boldformat.setBorder(Border.RIGHT, BorderLineStyle.THIN);
		       arial10boldformat.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
		      //style for bold and #.## format number cells
		       WritableCellFormat twodpsFormat = new WritableCellFormat(arial10boldfont,NumberFormats.FLOAT); 
		       twodpsFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		       twodpsFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
		       twodpsFormat.setWrap(true);
		       // style for standart first colunm cells
		       WritableCellFormat stan1colcell = new WritableCellFormat();
		       stan1colcell.setBorder(Border.LEFT, BorderLineStyle.MEDIUM);
		       stan1colcell.setBorder(Border.RIGHT, BorderLineStyle.THIN);
		       stan1colcell.setBorder(Border.TOP, BorderLineStyle.THIN);
		       stan1colcell.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
		       stan1colcell.setAlignment(Alignment.LEFT);
		       stan1colcell.setVerticalAlignment(VerticalAlignment.CENTRE);
		       stan1colcell.setWrap(true);
		       //style for bold first colunm cells
		       WritableCellFormat bold1colcell = new WritableCellFormat(arial10boldfont);
		       bold1colcell.setBorder(Border.LEFT, BorderLineStyle.MEDIUM,Colour.BLACK);
		       bold1colcell.setBorder(Border.BOTTOM, BorderLineStyle.THIN,Colour.BLACK);
		       bold1colcell.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		       bold1colcell.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		       bold1colcell.setAlignment(Alignment.CENTRE);
		       bold1colcell.setVerticalAlignment(VerticalAlignment.CENTRE);
		       bold1colcell.setWrap(true);
		       //style for standart number cells
		       WritableCellFormat stnumcell = new WritableCellFormat(NumberFormats.FLOAT);
		       stnumcell.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		       stnumcell.setAlignment(Alignment.RIGHT);
		       stnumcell.setVerticalAlignment(VerticalAlignment.CENTRE);
		       stnumcell.setWrap(true);
		       //Style for total cells
		       WritableCellFormat totalcell = new WritableCellFormat(arial10boldfont,NumberFormats.FLOAT);
		       totalcell.setBorder(Border.ALL, BorderLineStyle.MEDIUM, Colour.BLACK);
		       totalcell.setAlignment(Alignment.RIGHT);
		       totalcell.setVerticalAlignment(VerticalAlignment.CENTRE);
		       totalcell.setWrap(true);
		       //Style for header account name cells
		       WritableCellFormat accnamecell = new WritableCellFormat(arial10boldfont);
		       accnamecell.setBorder(Border.TOP, BorderLineStyle.MEDIUM, Colour.BLACK);
		       accnamecell.setBorder(Border.RIGHT, BorderLineStyle.MEDIUM, Colour.BLACK);
		       accnamecell.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		       accnamecell.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		       accnamecell.setAlignment(Alignment.CENTRE);
		       accnamecell.setVerticalAlignment(VerticalAlignment.CENTRE);
		       accnamecell.setWrap(true);
		       //Style for account name cell
		       WritableCellFormat accname = new WritableCellFormat();
		       accname.setBorder(Border.RIGHT, BorderLineStyle.MEDIUM, Colour.BLACK);
		       //Style for colunm header cells
		       WritableCellFormat colheadcell = new WritableCellFormat(arial10boldfont);
		       colheadcell.setBorder(Border.ALL, BorderLineStyle.THIN,Colour.BLACK);
		       colheadcell.setAlignment(Alignment.CENTRE);
		       colheadcell.setVerticalAlignment(VerticalAlignment.CENTRE);
		       //Style for last colunm header cells
		       WritableCellFormat lastcolheadcell = new WritableCellFormat(arial10boldfont);
		       lastcolheadcell.setAlignment(Alignment.CENTRE);
		       lastcolheadcell.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		       lastcolheadcell.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		       lastcolheadcell.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		       lastcolheadcell.setBorder(Border.RIGHT, BorderLineStyle.MEDIUM,Colour.BLACK);
		       //Style for standart last number cell
		       WritableCellFormat lastnumcell = new WritableCellFormat(NumberFormats.FLOAT);
		       lastnumcell.setBorder(Border.RIGHT, BorderLineStyle.MEDIUM, Colour.BLACK);
		       lastnumcell.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		       lastnumcell.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		       lastnumcell.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		       lastnumcell.setVerticalAlignment(VerticalAlignment.CENTRE);
		       lastnumcell.setWrap(true);
		       //Style for bold last number colunm cells
		       WritableCellFormat boldlastnumcell = new WritableCellFormat(arial10boldfont,NumberFormats.FLOAT);
		       boldlastnumcell.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		       boldlastnumcell.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		       boldlastnumcell.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		       boldlastnumcell.setBorder(Border.RIGHT,BorderLineStyle.MEDIUM,Colour.BLACK);
		       //Style for date 
		       WritableCellFormat datestyle = new WritableCellFormat(dateFormat);
		       datestyle.setAlignment(Alignment.LEFT);
		       datestyle.setVerticalAlignment(VerticalAlignment.CENTRE);
		       //Style for time
		       WritableCellFormat timestyle = new WritableCellFormat(timeFormat);
		       timestyle.setAlignment(Alignment.LEFT);
		       timestyle.setVerticalAlignment(VerticalAlignment.CENTRE);
		       //style for period
		       WritableCellFormat periodstyle = new WritableCellFormat(arial10boldfont);
		       periodstyle.setAlignment(Alignment.CENTRE);
		       //find start cell
		       int col = cellStart.getC(); 
		       int row = cellStart.getR();
		       // get account list
		       AccountValue[] lines =  getAccountValues();
		       AccountValue[] bplines = null;
		       AccountValue[] conlines = null;
		       /// for date to string
		       String date_From = format.format(DateAcct_From);
		       String date_To = format.format(DateAcct_To);
		      label = new Label(4, 2," c "+" "+date_From+" по "+date_To,periodstyle); sheet.addCell(label);
		      //paint report header1 and account value and name
		      int n=0; int endOfRep = 0;
		      for(int w=row;n<lines.length;w++){
		       label = new Label(col, w, " Счет ",arial10boldformat); sheet.addCell(label);
		       label = new Label(col+1, w, " Наименование счета ",accnamecell); sheet.addCell(label); sheet.mergeCells(col+1, w, col+7, w);
		       w++;
		       label = new Label(col, w, lines[n].getAccvalue(),stan1colcell); sheet.addCell(label);
		       label = new Label(col+1, w, lines[n].getAccname(),accname); sheet.addCell(label); sheet.mergeCells(col+1, w, col+7, w);
		       w++;
		       label = new Label(col, w, " Код ",bold1colcell); sheet.addCell(label); sheet.mergeCells(col, w, col, w+1);
		       label = new Label(col+1, w, " Субконто ",colheadcell); sheet.addCell(label); sheet.mergeCells(col+1, w, col+1, w+1);
		       label = new Label(col+2, w, " Сальдо на начало периода ",colheadcell); sheet.addCell(label); sheet.mergeCells(col+2, w, col+3, w);
		       label = new Label(col+4, w, " Обороты за период ",colheadcell); sheet.addCell(label); sheet.mergeCells(col+4, w, col+5, w);
		       label = new Label(col+6, w, " Сальдо на конец периода ",lastcolheadcell); sheet.addCell(label); sheet.mergeCells(col+6, w, col+7, w);
		       w++;
		       label = new Label(col+2, w, " Дебет ",colheadcell); sheet.addCell(label);
		       label = new Label(col+3, w, " Кредит ",colheadcell); sheet.addCell(label);
		       label = new Label(col+4, w, " Дебет ",colheadcell); sheet.addCell(label);
		       label = new Label(col+5, w, " Кредит ",colheadcell); sheet.addCell(label);
		       label = new Label(col+6, w, " Дебет ",colheadcell); sheet.addCell(label);
		       label = new Label(col+7, w, " Кредит ",lastcolheadcell); sheet.addCell(label);
		       //for sum total
		       double sumDr1 = 0;
		       double sumCr1 = 0;
		       double sumDr2 = 0;
		       double sumCr2 = 0;
		       double sumDr3 = 0;
		       double sumCr3 = 0;
		       			// get Bpartner list
				       bplines = getBpartnerValues(lines[n].getAccount_id());
				       int m=0; int rowcount=w;
				       for(int j=w+1;m<bplines.length;j++){
				       label = new Label(col, j, bplines[m].getBpvalue(),bold1colcell); sheet.addCell(label); 
				       label = new Label(col+1, j, bplines[m].getBpname(),twodpsFormat);sheet.addCell(label);
				       number= new Number(col+2, j, bplines[m].getDr1(),twodpsFormat);sheet.addCell(number);
				       number= new Number(col+3, j, bplines[m].getCr1(),twodpsFormat);sheet.addCell(number);
				       number= new Number(col+4, j, bplines[m].getDr2(),twodpsFormat);sheet.addCell(number);
				       number= new Number(col+5, j, bplines[m].getCr2(),twodpsFormat);sheet.addCell(number);
				       number= new Number(col+6, j, bplines[m].getDr3(),twodpsFormat);sheet.addCell(number);
				       number= new Number(col+7, j, bplines[m].getCr3(),boldlastnumcell);sheet.addCell(number);
				       			///get userelement1 list
						       conlines = getContractValues(lines[0].getAccount_id(), bplines[m].getC_bpartner_id());
						       int p=0;
						       for(int k=j+1;p<conlines.length;k++){
						       label = new Label(col, k, conlines[p].getContract(),stan1colcell); sheet.addCell(label); 
						       label = new Label(col+1, k, conlines[p].getCmsdescription(),standartstyle);sheet.addCell(label);
						       number = new Number(col+2, k, conlines[p].getDr1(),stnumcell); sheet.addCell(number);
						       number = new Number(col+3, k, conlines[p].getCr1(),stnumcell); sheet.addCell(number);
						       number = new Number(col+4, k, conlines[p].getDr2(),stnumcell); sheet.addCell(number);
						       number = new Number(col+5, k, conlines[p].getCr2(),stnumcell); sheet.addCell(number);
						       number = new Number(col+6, k, conlines[p].getDr3(),stnumcell); sheet.addCell(number);
						       number = new Number(col+7, k, conlines[p].getCr3(),lastnumcell); sheet.addCell(number);
						       p++;rowcount++;w++; 
						       }
						   // calc total    
					       sumDr1+=bplines[m].getDr1(); sumDr2+=bplines[m].getDr2(); sumDr3+=bplines[m].getDr3();
					       sumCr1+=bplines[m].getCr1(); sumCr2+=bplines[m].getCr2(); sumCr3+=bplines[m].getCr3();
					       m++;j+=p;rowcount++; //w++;
				       }
				       //view total cells
				       label = new Label(col, rowcount+1, "",totalcell); sheet.addCell(label);
				       label = new Label(col+1, rowcount+1, " Итого: ",totalcell); sheet.addCell(label);
				       number = new Number(col+2, rowcount+1, sumDr1,totalcell); sheet.addCell(number);
				       number = new Number(col+3, rowcount+1, sumCr1,totalcell); sheet.addCell(number);
				       number = new Number(col+4, rowcount+1, sumDr2,totalcell); sheet.addCell(number);
				       number = new Number(col+5, rowcount+1, sumCr2,totalcell); sheet.addCell(number);
				       number = new Number(col+6, rowcount+1, sumDr3,totalcell); sheet.addCell(number);
				       number = new Number(col+7, rowcount+1, sumCr3,totalcell); sheet.addCell(number);
				  n++;w+=m;  w+=4;  endOfRep=w;
		      }
		      ///current date and time
		      DateTime dateCell = new DateTime(col+6, endOfRep+1, now, datestyle); sheet.addCell(dateCell);
		      DateTime timeCell = new DateTime(col+7, endOfRep+1, now, timestyle); sheet.addCell(timeCell);
		      //Username
		      MUser u = new MUser(getCtx(),Env.getAD_User_ID(getCtx()), get_TrxName());
		      label = new Label(col+6, endOfRep+2, u.getC_BPartner().getName2()); sheet.addCell(label);
		       //write to excel sheet
		       copy.write();
		       copy.close();
		       tableBook.close();
		       temporaryFile.delete();
		       
		    // Open the file with the application associated
				String fileName = fullPath.toString();
				Util.launchFile(new File(fileName));
				
		       return Msg.translate(m_ctx, "Success");
	}
	
	private AccountValue[] getAccountValues(){

		//
		AccountValue vacLine = null;
    	List<AccountValue> list = new ArrayList<AccountValue>();
    	//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;		
			
		//
			try
			{
				pstmt = DB.prepareStatement(getAccountSql(),null);
				pstmt.setTimestamp(1, DateAcct_From);
				pstmt.setTimestamp(2, DateAcct_From);
				pstmt.setTimestamp(3, DateAcct_To);
				pstmt.setInt(4, Env.getAD_Client_ID(Env.getCtx()));
				pstmt.setInt(5, Env.getAD_Org_ID(Env.getCtx()));
				if(AccountSign!=null)
					pstmt.setString(6, AccountSign);
				if(Account_ID!=0)
					pstmt.setInt(7, Account_ID);
				rs = pstmt.executeQuery();	
				while (rs.next ())
				{
					vacLine = new AccountValue();
					vacLine.setAd_client_id(rs.getInt(1));
					vacLine.setAd_org_id(rs.getInt(2));
					vacLine.setAccount_id(rs.getInt(3));
					vacLine.setAccvalue(rs.getString(4));
					vacLine.setAccname(rs.getString(5));
					list.add(vacLine);
					vacLine = null;
				}
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}	
			return list.toArray(new AccountValue[list.size()]);
	}
	
	private AccountValue[] getBpartnerValues(int account_id){
		//
		AccountValue vacLine = null;
    	List<AccountValue> list = new ArrayList<AccountValue>();
    	//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;			
		//
			try
			{
				pstmt = DB.prepareStatement(getBPartnerSql(),null);
				pstmt.setTimestamp(1, DateAcct_From);
				pstmt.setInt(2, account_id);
				pstmt.setTimestamp(3, DateAcct_From);
				pstmt.setTimestamp(4, DateAcct_To);
				pstmt.setInt(5, account_id);
				if(C_BPartner_ID!=0)
					pstmt.setInt(6, C_BPartner_ID);
				rs = pstmt.executeQuery();	
				while (rs.next ())
				{
					vacLine = new AccountValue();
					vacLine.setC_bpartner_id(rs.getInt(1));
					vacLine.setBpvalue(rs.getString(2));
					vacLine.setBpname(rs.getString(3));
					vacLine.setDr1(rs.getDouble(4));
					vacLine.setCr1(rs.getDouble(5));
					vacLine.setDr2(rs.getDouble(6));
					vacLine.setCr2(rs.getDouble(7));
					vacLine.setDr3(rs.getDouble(8));
					vacLine.setCr3(rs.getDouble(9));
					list.add(vacLine);
					vacLine = null;
				}
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}	
			return list.toArray(new AccountValue[list.size()]);
	}
	
	private AccountValue[] getContractValues(int account_id,int c_bpartner_id){
		//
		AccountValue vacLine = null;
    	List<AccountValue> list = new ArrayList<AccountValue>();
    	//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;			
		//
			try
			{
				pstmt = DB.prepareStatement(getContractSql(),null);
				pstmt.setTimestamp(1, DateAcct_From);
				pstmt.setInt(2, account_id);
				pstmt.setInt(3, c_bpartner_id);
				pstmt.setTimestamp(4, DateAcct_From);
				pstmt.setTimestamp(5, DateAcct_To);
				pstmt.setInt(6, account_id);
				pstmt.setInt(7, c_bpartner_id);
				if(UserElement1_ID!=0)
					pstmt.setInt(8, UserElement1_ID);
				rs = pstmt.executeQuery();	
				while (rs.next ())
				{
					vacLine = new AccountValue();
					vacLine.setUserelement1_id(rs.getInt(1));
					vacLine.setContract(rs.getString(2));
					vacLine.setCmsdescription(rs.getString(3));
					vacLine.setDr1(rs.getDouble(4));
					vacLine.setCr1(rs.getDouble(5));
					vacLine.setDr2(rs.getDouble(6));
					vacLine.setCr2(rs.getDouble(7));
					vacLine.setDr3(rs.getDouble(8));
					vacLine.setCr3(rs.getDouble(9));
					list.add(vacLine);
					vacLine = null;
				}
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}	
			return list.toArray(new AccountValue[list.size()]);
	}

	/** sql for get account list in period*/
	private String getAccountSql(){
		StringBuffer sql = new StringBuffer();
		sql.append(	    " WITH main AS("
						+" SELECT ad_client_id,ad_org_id,account_id FROM adempiere.fact_acct where dateacct<? GROUP BY ad_client_id,ad_org_id,account_id  UNION ALL" //1
						+" SELECT ad_client_id,ad_org_id,account_id FROM adempiere.fact_acct where dateacct BETWEEN ? AND ? GROUP BY ad_client_id,ad_org_id,account_id)" //2,3
						+" SELECT main.ad_client_id,main.ad_org_id,main.account_id,c.value AS accvalue,c.description AS accname FROM main,adempiere.c_elementvalue c"
						+" WHERE c.c_elementvalue_id = main.account_id"
						+" AND main.ad_client_id=?" //4
						+" AND main.ad_org_id=?"); //5
		if(AccountSign!=null)
			sql.append(" AND c.accountsign=?"); //6
		if(Account_ID!=0)
			sql.append(" AND main.account_id=?"); //7
		sql.append(" GROUP BY  main.ad_client_id,main.ad_org_id,account_id,c.value,c.description  ORDER BY  c.value");
		return sql.toString();
	}
	/**  sql for get bpartner list in period*/
	private String getBPartnerSql(){
		StringBuffer sql = new StringBuffer();
			sql.append(	"WITH main AS ("
						+" SELECT c_bpartner_id,sum(amtacctdr) AS dr1, sum(amtacctcr) AS cr1,0::numeric AS dr2, 0::numeric AS cr2 from adempiere.fact_acct" 
						+" WHERE dateacct<? AND account_id=? GROUP BY c_bpartner_id  UNION ALL" //1,2
						+" SELECT c_bpartner_id,0::numeric AS dr1,0::numeric AS cr1,sum(amtacctdr) AS dr2, sum(amtacctcr) AS cr2 from adempiere.fact_acct" 
						+" WHERE dateacct BETWEEN ? AND ?  AND account_id=? GROUP BY c_bpartner_id)" //3,4,5
						+" SELECT main.c_bpartner_id,bp.value AS bpvalue,bp.name AS bpname, "
						+" (CASE WHEN (COALESCE(sum(main.dr1),0)-COALESCE(sum(main.cr1),0))<0 THEN 0 ELSE (COALESCE(sum(main.dr1),0)-COALESCE(sum(main.cr1),0)) END) AS dr1,"
						+" (CASE WHEN (COALESCE(sum(main.dr1),0)-COALESCE(sum(main.cr1),0))<0 THEN -1*(COALESCE(sum(main.dr1),0)-COALESCE(sum(main.cr1),0)) ELSE 0 END) AS cr1,"
						+" COALESCE(sum(main.dr2),0) AS dr2,"
						+" COALESCE(sum(main.cr2),0) AS cr2,"
						+" (CASE WHEN((COALESCE(sum(main.dr1),0)+COALESCE(sum(main.dr2),0))-(COALESCE(sum(main.cr1),0)+COALESCE(sum(main.cr2),0)))<0"
						+" THEN 0 ELSE ((COALESCE(sum(main.dr1),0)+COALESCE(sum(main.dr2),0))-(COALESCE(sum(main.cr1),0)+COALESCE(sum(main.cr2),0)))END) AS dr3,"
						+" (CASE WHEN((COALESCE(sum(main.dr1),0)+COALESCE(sum(main.dr2),0))-(COALESCE(sum(main.cr1),0)+COALESCE(sum(main.cr2),0)))<0"
						+" THEN -1*((COALESCE(sum(main.dr1),0)+COALESCE(sum(main.dr2),0))-(COALESCE(sum(main.cr1),0)+COALESCE(sum(main.cr2),0))) ELSE 0 END) AS cr3" 
						+" FROM main,adempiere.c_bpartner bp"
						+" WHERE bp.c_bpartner_id=main.c_bpartner_id ");
			if(C_BPartner_ID!=0)
				sql.append(" AND main.c_bpartner_id=?"); //6
			sql.append(" GROUP BY main.c_bpartner_id,bp.value,bp.name");
		return sql.toString();
	}
	/**  sql for get contract list in period*/
	private String getContractSql(){
		StringBuffer sql = new StringBuffer();
		sql.append(	    "WITH main AS ("
						+" SELECT userelement1_id,sum(amtacctdr) as dr1, sum(amtacctcr) as cr1,0::numeric as dr2, 0::numeric as cr2 FROM adempiere.fact_acct" 
						+" WHERE dateacct<? AND account_id=? and c_bpartner_id=? GROUP BY userelement1_id  UNION ALL" //1,2,3
						+" SELECT userelement1_id,0::numeric as dr1,0::numeric as cr1,sum(amtacctdr) as dr2, sum(amtacctcr) as cr2 FROM adempiere.fact_acct" 
						+" WHERE dateacct BETWEEN ? AND ?  AND account_id=? AND c_bpartner_id=? GROUP BY userelement1_id)"//4,5,6,7
						+" SELECT main.userelement1_id,cms.cms_contract_contractnum AS contract,cms.description AS cmsdescription, "
						+" (CASE WHEN (COALESCE(sum(main.dr1),0)-COALESCE(sum(main.cr1),0))<0 THEN 0 ELSE (COALESCE(sum(main.dr1),0)-COALESCE(sum(main.cr1),0)) END) AS dr1,"
						+" (CASE WHEN (COALESCE(sum(main.dr1),0)-COALESCE(sum(main.cr1),0))<0 THEN -1*(COALESCE(sum(main.dr1),0)-COALESCE(sum(main.cr1),0)) ELSE 0 END) AS cr1,"
						+" COALESCE(sum(main.dr2),0) AS dr2,"
						+" COALESCE(sum(main.cr2),0) AS cr2,"
						+" (CASE WHEN((COALESCE(sum(main.dr1),0)+COALESCE(sum(main.dr2),0))-(COALESCE(sum(main.cr1),0)+COALESCE(sum(main.cr2),0)))<0"
						+" THEN 0 ELSE ((COALESCE(sum(main.dr1),0)+COALESCE(sum(main.dr2),0))-(COALESCE(sum(main.cr1),0)+COALESCE(sum(main.cr2),0)))END) AS dr3,"
						+" (CASE WHEN((COALESCE(sum(main.dr1),0)+COALESCE(sum(main.dr2),0))-(COALESCE(sum(main.cr1),0)+COALESCE(sum(main.cr2),0)))<0"
						+" THEN -1*((COALESCE(sum(main.dr1),0)+COALESCE(sum(main.dr2),0))-(COALESCE(sum(main.cr1),0)+COALESCE(sum(main.cr2),0))) ELSE 0 END) AS cr3"
						+" FROM main,adempiere.cms_contract cms "
						+" WHERE cms.cms_contract_id=main.userelement1_id");
		if(UserElement1_ID!=0)
			sql.append(" AND main.userelement1_id=?"); //8
		sql.append(" GROUP BY main.userelement1_id,cms_contract_contractnum,cms.description");
	return sql.toString();
	}

	
	private class AccountValue{

		private int ad_client_id;
		private int ad_org_id;
		private int account_id;
		private int c_bpartner_id;
		private int userelement1_id;
		private double dr1;
		private double cr1;
		private double dr2;
		private double cr2;
		private double dr3;
		private double cr3;
		private String accvalue;
		private String accname;
		private String bpvalue;
		private String bpname;
		private String contract;
		private String cmsdescription;
		
		public int getAd_client_id() {
			return ad_client_id;
		}
		public void setAd_client_id(int ad_client_id) {
			this.ad_client_id = ad_client_id;
		}
		public int getAd_org_id() {
			return ad_org_id;
		}
		public void setAd_org_id(int ad_org_id) {
			this.ad_org_id = ad_org_id;
		}
		public int getAccount_id() {
			return account_id;
		}
		public void setAccount_id(int account_id) {
			this.account_id = account_id;
		}
		public int getC_bpartner_id() {
			return c_bpartner_id;
		}
		public void setC_bpartner_id(int c_bpartner_id) {
			this.c_bpartner_id = c_bpartner_id;
		}
		public int getUserelement1_id() {
			return userelement1_id;
		}
		public void setUserelement1_id(int userelement1_id) {
			this.userelement1_id = userelement1_id;
		}
		public double getDr1() {
			return dr1;
		}
		public void setDr1(double dr1) {
			this.dr1 = dr1;
		}
		public double getCr1() {
			return cr1;
		}
		public void setCr1(double cr1) {
			this.cr1 = cr1;
		}
		public double getDr2() {
			return dr2;
		}
		public void setDr2(double dr2) {
			this.dr2 = dr2;
		}
		public double getCr2() {
			return cr2;
		}
		public void setCr2(double cr2) {
			this.cr2 = cr2;
		}
		public double getDr3() {
			return dr3;
		}
		public void setDr3(double dr3) {
			this.dr3 = dr3;
		}
		public double getCr3() {
			return cr3;
		}
		public void setCr3(double cr3) {
			this.cr3 = cr3;
		}
		public String getAccvalue() {
			return accvalue;
		}
		public void setAccvalue(String accvalue) {
			this.accvalue = accvalue;
		}
		public String getAccname() {
			return accname;
		}
		public void setAccname(String accname) {
			this.accname = accname;
		}
		public String getBpvalue() {
			return bpvalue;
		}
		public void setBpvalue(String bpvalue) {
			this.bpvalue = bpvalue;
		}
		public String getBpname() {
			return bpname;
		}
		public void setBpname(String bpname) {
			this.bpname = bpname;
		}
		public String getContract() {
			return contract;
		}
		public void setContract(String contract) {
			this.contract = contract;
		}
		public String getCmsdescription() {
			return cmsdescription;
		}
		public void setCmsdescription(String cmsdescription) {
			this.cmsdescription = cmsdescription;}	} }
